The DataTable Class

A DataTable contains a set of data in row/column format. The various constructors and methods allow you to control how that data is loaded from the database, and/or how it should be written to the database.

If you make any changes to the data contained within a DataTable, it is your responsibility to write the changed data to the underlying database or file, by calling either of the methods, WriteChanges or TryWriteChanges (described below).

The DataTable's indexing operator (square brackets) returns the row at the specified row position (starting from zero). Furthermore, enumerating through a DataTable returns each of its rows in succession. So, if you want to iterate though all of the rows in a DataTable, you can use either of these techniques:

// Obtain a data table somehow.
 
DataTable tbl = new DataTable(connection, 'SELECT * FROM Table1');
 
// Iterate through all of the rows – technique #1:
 
for (int i = 0; i < tbl.Count; ++i)
{
    DataRow row = tbl[i];
    :  // Do something with the row.
}
 
// Iterate through all of the rows – technique #2:
 
foreach (DataRow row in tbl)
{
    :  // Do something with the row.
}

Note that you cannot assign to a table’s indexer (nor is there ever a need to):

// Obtain a data table somehow.
 
DataTable tbl = new DataTable(connection, 'SELECT * FROM Table1');
 
// Copy the contents of row 2 to row 6 (the wrong way).
 
DataRow row2 = tbl[2];
tbl[6] = row2;         // NO! This will throw an exception at runtime.
                       // You can’t assign to a DataTable’s indexer.
 
// Copy the contents of row 2 to row 6 (the right way).
 
DataRow row2 = tbl[2];
DataRow row6 = tbl[6];
row6.SetValuesFrom(row2);
 
// Alternately, you could do it in one statement:
 
tbl[6].SetValuesFrom(tbl[2]);

Here are the constructors, properties and methods of the DataTable class:

new DataTable(connection, sqlSelectComand)

This constructor creates and loads a data table from a DataConnection object and a SQL SELECT command.

The arguments are:

DataConnection connection

A DataConnection object that you've previously created (and which is still open).

The same connection can be used to create multiple DataTable objects, each with its own SQL SELECT command.

string sqlSelectCommand

A Transact-SQL SELECT command. For more information, please consult documentation for Microsoft's Transact-SQL language.

If the data connection is connected to a text file, the table name should be the name of that text file, including extension.

This command is used not only for loading the data initially, but also for updating the data. (Internally, ADO.NET's CommandBuilder class extrapolates update commands from your SELECT command.)

new DataTable(tableType, filePath [, columnTypes [, columnNames [, columnWidths]]] )

This constructor creates and loads a "connection-less" data table, from a simple text file.

Currently, this supports only one file format, which is the native XML format of ADO.NET's DataSet class. Future versions of GC may support additional formats.

Note: You can work with a comma-delimited text file by creating a DataConnection whose DatabaseType is CommaSeparatedText.

The arguments are:

DataTableType tableType

Currently, there is only one possible value, XmlWithSchema.

string filePath

The full path and file name of the text file.

ColumnTypes: Type[] - optional

ColumnNames: string[] - optional

ColumnWidths: int[] - optional

This set of optional properties serves two purposes:

If this is a new text file, for which no other schema information exists, these properties define the schema.
If this is an existing text file, these properties provide an "override" of the schema information that's stored in the file.

For example, the columns in the file are named 'x', 'y' and 'z', but you want them to be named 'Xtranslation', 'Ytranslation' and 'Ztranslation' within the DataTable.

If you don't specify any column information, the columns in the DataTable are the same as those defined in the file.

bool CanWriteRowDeletions()

This method returns whether individual row deletions can be written back to the underlying database or file.

Generally, that's possible only when the underlying database table has a key column that was also loaded into this DataTable.

bool CanWriteRowRevisions()

This method returns whether changes to the existing data can be written back to the underlying database.

Generally, that's possible only when the underlying database table has a key column that was also loaded into this DataTable.

DataColumn[] Columns { get; }

This read-only property gives the list of the data columns within this table. (The DataColumn class is described below.)

DataConnection Connection { get; }

This read-only property gives this table's associated data connection, if any.

int Count { get; }

For a DataTable object, the Count property returns the number of data rows

void DeleteAllData()

This method deletes all of the data in this DataTable object. The column structure is retained.

Furthermore, this method deletes all of the data in the underlying database table or file!

(It is as though this method includes an implicit call to the WriteChanges method.)

bool HasChanged { get; }

This read-only property indicates whether the data stored in this DataTable is different from the data that's stored in the underlying database or file.

DataRow NewRow()

This method adds a new, empty data row to the end of the table, and returns that data row. (The DataRow class is described below.)

void ReloadData()

This method discards all of this DataTable's data, then reloads it from the underlying database or file. Any changes you had made are lost.

bool TryWriteChanges()

If you've made any changes to the data contained within this DataTable, this method writes the changed data to the underlying database or file.

If the changes are written successfully, this method returns true.

If, for any reason, the changes cannot be written, this method returns false (and provides no other information about the nature of the problem).

void WriteChanges()

If you've made any changes to the data contained within this DataTable, this method writes the changed data to the underlying database or file.

If, for any reason, the changes cannot be written, this method throws an exception.